/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package data;

import business.*;
import java.util.ArrayList;
import java.sql.SQLException;
import java.sql.ResultSet;


public class ServicosData extends InterfaceBD{

    public ServicosData() {
        super();
    }

    public void addServico( String descricao, String nome, double preco) throws Exception{
        int codS = getnextCodServ();
        String query = "Insert Into Servico VALUES("+codS+",'"+nome+"','"+descricao+"',"+preco+")";
        System.out.println(query);
        res = st.executeUpdate(query);

    }

    public void removerServico(int codigo) throws Exception{
        String query = "Delete from Servico where cod_servico ="+codigo;
        System.out.println(query);
        res = st.executeUpdate(query);
    }

    public void alterarOpcoesServicos(int cod,String nome,String desc) throws SQLException {
        boolean updatenome = !(nome.isEmpty());
        boolean updatedesc = !(desc.isEmpty());
        String query;
        if(updatenome && updatedesc){
        query = "Update Servicos SET nomeserv ='"+ nome + "',descserv ='"+desc+"' where codserv= "+cod;
        }else if(updatenome){
        query = "Update Servicos SET nomeserv ='"+ nome + "' where codserv= "+cod;
        }else{
        query = "Update Servicos SET descserv ='"+ desc + "' where codserv= "+cod;
        }
        System.out.println(query);
        res = st.executeUpdate(query);


    }

    public void setPrecoServico(int codServico,double preco) throws SQLException {
        String query = "UPDATE Servicos Set precoserv="+preco+" where cod_servico="+codServico;
        System.out.println(query);
        res = st.executeUpdate(query);
    }

     public void comprarServico(int codCliente,int codServico) throws SQLException {
         String query = "Insert Into ServicosSubscritos VALUES("+codServico+","+codCliente+")";
         System.out.println(query);
         res = st.executeUpdate(query);

    }



    public void cancelarServico(int codCliente,int codServico) throws SQLException {
        String query = "Delete from ServicosSubscritos where cod_servss="+codServico+" and cod_uss="+codCliente;
        System.out.println(query);
        res = st.executeUpdate(query);

    }



    public ArrayList<Servico> consultarServCliente(int codCliente) throws SQLException {
        ArrayList<Servico> lista = new ArrayList<Servico>();
        String query= "Select cod_servss, nomeserv,precoserv,descserv from Servico,ServicosSubscritos where cod_servss=cod_servss"+" and cod_uss="+codCliente;
        System.out.println(query);
        rs = st.executeQuery(query);
        while(rs.next()){
            lista.add(new Servico(rs.getString(2),rs.getInt(1),rs.getDouble(3),rs.getString(4)));
        }
        return lista;
    }



    public ArrayList<Servico> consultarServicos() throws SQLException {
        ArrayList<Servico> lista = new ArrayList<Servico>();
        String query = "Select * from Servico";
        System.out.println(query);
        rs = st.executeQuery(query);
        while(rs.next()){
            lista.add(new Servico(rs.getString(2),rs.getInt(1),rs.getDouble(4),rs.getString(3)));
        }

        return lista;
    }
    

    public int getnextCodServ() throws SQLException {

        ResultSet rs;
        int num = 0;

        try {
            rs = st.executeQuery("SELECT MAX(cod_serv) FROM Servico");
            rs.next();
            num = rs.getInt(1);
        }
        catch (SQLException e) {

            //
        }
        return(num+1);
    }

}
